package uy.edu.um.ltic.server.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

import uy.edu.um.ltic.exception.DataBaseException;
import uy.edu.um.ltic.server.business.ConferenceMessage;
import uy.edu.um.ltic.server.business.Employee;
import uy.edu.um.ltic.server.business.FileMessage;
import uy.edu.um.ltic.server.business.Message;
import uy.edu.um.ltic.server.business.TextMessage;
import uy.edu.um.ltic.server.communication.ServerSocket;
import uy.edu.um.ltic.server.pool.DataBaseConnectionMgr;

public class MessageDAOMgr implements MessageDAOMgt {

	private static MessageDAOMgr instance = null;

	private static final String SELEMPALLMSG = "e.name, e.surname, e.document, "
			+ "e.nEmployee, e.positionInCompany, e.currentCountry, e.branch, "
			+ "e.department, e.username, e.password, e.documentType, e.image, "
			+ "e.availability, e.email, e.administrator, e.salt";

	private MessageDAOMgr() {
	}

	public static MessageDAOMgr getInstance() {
		if (instance == null) {
			instance = new MessageDAOMgr();
		}
		return instance;
	}

	@Override
	public void addMessage(Message oMessage) throws DataBaseException {
		if (oMessage instanceof TextMessage) {
			addTextMessage(oMessage);
			for (Employee emp : oMessage.getReceivers()) {
				ServerSocket.notifyClients(
						Integer.toString(emp.getnEmployee()),
						Integer.toString(oMessage.getSender().getnEmployee()),
						"refrChat");
			}
		} else if (oMessage instanceof FileMessage) {
			addFileMessage(oMessage);
			for (Employee emp : oMessage.getReceivers()) {
				ServerSocket.notifyClients(
						Integer.toString(emp.getnEmployee()),
						Integer.toString(oMessage.getSender().getnEmployee()),
						"refrChat");
			}
		} else if (oMessage instanceof ConferenceMessage) {
			addConferenceMessage(oMessage);
			for (Employee emp : ConferenceDAOFactory
					.getInstance()
					.getConferenceDAOMgt()
					.getAllParticipants(
							((ConferenceMessage) oMessage).getIdConference())) {
				ServerSocket.notifyClients(
						Integer.toString(emp.getnEmployee()), Integer
								.toString(((ConferenceMessage) oMessage)
										.getIdConference()), "refrChatConf");
			}
		}
	}

	@Override
	public ArrayList<Message> getMessages(Employee sender,
			ArrayList<Employee> receivers) throws DataBaseException {
		ArrayList<Message> oList = new ArrayList<Message>();
		ArrayList<Employee> oList2 = new ArrayList<Employee>();
		Employee senderFinal;
		Message finalMessage = null;
		Connection oConnection = this.openConnection();
		for (int i = 0; i < receivers.size(); i++) {
			String receptors = "SELECT DISTINCT "
					+ SELEMPALLMSG
					+ " FROM TextEmployees t, Employees e, TextMessages tm "
					+ "WHERE t.receivers = e.nEmployee AND tm.idMessage = t.idMessage "
					+ "AND tm.sender = '" + sender.getnEmployee()
					+ "' AND receivers = '" + receivers.get(i).getnEmployee()
					+ "';";
			;
			Statement oStatement;
			try {
				oStatement = oConnection.createStatement();
				ResultSet oResult = oStatement.executeQuery(receptors);
				while (oResult.next()) {
					if (oResult.getBlob(12) == null) {
						oList2.add(new Employee(oResult.getString(1), oResult
								.getString(2), oResult.getInt(3), oResult
								.getInt(4), oResult.getString(5), oResult
								.getString(6), oResult.getString(7), oResult
								.getString(8), oResult.getString(9), oResult
								.getString(10), oResult.getString(11), null,
								oResult.getString(13), oResult.getString(14),
								oResult.getBoolean(15), oResult.getString(16)));
					} else {
						oList2.add(new Employee(oResult.getString(1), oResult
								.getString(2), oResult.getInt(3), oResult
								.getInt(4), oResult.getString(5), oResult
								.getString(6), oResult.getString(7), oResult
								.getString(8), oResult.getString(9), oResult
								.getString(10), oResult.getString(11), oResult
								.getBlob(12).getBytes(1,
										(int) oResult.getBlob(12).length()),
								oResult.getString(13), oResult.getString(14),
								oResult.getBoolean(15), oResult.getString(16)));
					}
					/*
					 * "e.name, e.surname, e.document, " +
					 * "e.nEmployee, e.positionInCompany, e.currentCountry, e.branch, "
					 * +
					 * "e.department, e.username, e.password, e.documentType, e.image, "
					 * + "e.availability, e.email, e.administrator, e.salt"
					 */
				}
				oResult.close();

			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
		for (int i = 0; i < receivers.size(); i++) {
			String otherInfo = "SELECT DISTINCT "
					+ SELEMPALLMSG
					+ ", tm.date, tm.text "
					+ "FROM TextEmployees t, Employees e, TextMessages tm "
					+ "WHERE tm.sender = e.nEmployee AND tm.idMessage = t.idMessage "
					+ "AND tm.sender = '" + sender.getnEmployee()
					+ "' AND receivers = '" + receivers.get(i).getnEmployee()
					+ "' ORDER BY date, nEmployee;";
			Statement oStatement;
			try {
				oStatement = oConnection.createStatement();
				ResultSet oResult = oStatement.executeQuery(otherInfo);
				while (oResult.next()) {
					if (oResult.getBlob(12) == null) {
						senderFinal = new Employee(oResult.getString(1),
								oResult.getString(2), oResult.getInt(3),
								oResult.getInt(4), oResult.getString(5),
								oResult.getString(6), oResult.getString(7),
								oResult.getString(8), oResult.getString(9),
								oResult.getString(10), oResult.getString(11),
								null, oResult.getString(13),
								oResult.getString(14), oResult.getBoolean(15),
								oResult.getString(16));
					} else {
						senderFinal = new Employee(oResult.getString(1),
								oResult.getString(2), oResult.getInt(3),
								oResult.getInt(4), oResult.getString(5),
								oResult.getString(6), oResult.getString(7),
								oResult.getString(8), oResult.getString(9),
								oResult.getString(10), oResult.getString(11),
								oResult.getBlob(12).getBytes(1,
										(int) oResult.getBlob(12).length()),
								oResult.getString(13), oResult.getString(14),
								oResult.getBoolean(15), oResult.getString(16));
					}

					finalMessage = new TextMessage(oResult.getTimestamp(17),
							oList2, senderFinal, oResult.getString(18));
					oList.add(finalMessage);
				}
				oResult.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
		try {
			oConnection.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		}
		return oList;
	}

	public ArrayList<Message> getMessagesBetweenTwo(Employee windowBeingShown,
			Employee anotherOne) throws DataBaseException {
		ArrayList<Message> result = new ArrayList<Message>();
		ArrayList<Employee> oListEmployee = new ArrayList<Employee>();
		oListEmployee.add(anotherOne);
		ArrayList<Message> oList = getMessages(windowBeingShown, oListEmployee);
		for (int i = 0; i < oList.size(); i++) {
			result.add(oList.get(i));
		}
		ArrayList<Employee> oListWindow = new ArrayList<Employee>();
		oListWindow.add(windowBeingShown);
		ArrayList<Message> oList2 = getMessages(anotherOne, oListWindow);
		for (int i = 0; i < oList2.size(); i++) {
			result.add(oList2.get(i));
		}
		Collections.sort(result, new Comparator<Message>() {
			public int compare(Message s1, Message s2) {
				return s1.getDate().compareTo(s2.getDate());
			}
		});
		return result;
	}

	@Override
	public void createALLTablesMessages() throws DataBaseException {
		Connection oConnection = this.openConnection();
		createTableTextM(oConnection);
		createTableFileM(oConnection);
		createTableConferenceM(oConnection);
		try {
			oConnection.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		}
	}

	private void createTableTextM(Connection oConnection)
			throws DataBaseException {
		String createTableT = "CREATE TABLE TextMessages (idMessage INT AUTO_INCREMENT NOT NULL, "
				+ "sender INT, "
				+ "date TIMESTAMP, text VARCHAR(1000), PRIMARY KEY (idMessage), "
				+ "FOREIGN KEY (sender) REFERENCES Employees(nEmployee) ON DELETE CASCADE ON UPDATE CASCADE);";
		Statement oStatementT;
		try {
			oStatementT = oConnection.createStatement();
			oStatementT.execute(createTableT);
		} catch (SQLException e) {
			throw new DataBaseException();
		}
		createTableTextMessagesEmployee(oConnection);
	}

	// GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)

	private void createTableTextMessagesEmployee(Connection oConnection)
			throws DataBaseException {
		String createTableT = "CREATE TABLE TextEmployees (idMessage INT NOT NULL, "
				+ "receivers INT, PRIMARY KEY(idMessage, receivers), "
				+ "FOREIGN KEY (idMessage) REFERENCES TextMessages(idMessage) ON DELETE CASCADE ON UPDATE CASCADE, "
				+ "FOREIGN KEY (receivers) REFERENCES Employees(nEmployee) ON DELETE CASCADE ON UPDATE CASCADE);";
		Statement oStatementT;
		try {
			oStatementT = oConnection.createStatement();
			oStatementT.execute(createTableT);
		} catch (SQLException e) {
			throw new DataBaseException();
		}
	}

	private void createTableFileM(Connection oConnection)
			throws DataBaseException {
		String createTableF = "CREATE TABLE FileMessages (idMessage INT AUTO_INCREMENT NOT NULL, "
				+ "date TIMESTAMP, "
				+ "sender INT, "
				+ "file LONGBLOB, "
				+ "name VARCHAR(30), "
				+ "PRIMARY KEY (idMessage), "
				+ "FOREIGN KEY (sender) REFERENCES Employees(nEmployee) ON DELETE CASCADE ON UPDATE CASCADE);";
		Statement oStatementF;
		try {
			oStatementF = oConnection.createStatement();
			oStatementF.execute(createTableF);
		} catch (SQLException e) {
			throw new DataBaseException();
		}
		createTableFileEmployees(oConnection);
	}

	private void createTableFileEmployees(Connection oConnection)
			throws DataBaseException {
		String createTableT = "CREATE TABLE FileEmployees (idMessage INT NOT NULL, "
				+ "receivers INT, PRIMARY KEY(idMessage, receivers), "
				+ "FOREIGN KEY (idMessage) REFERENCES FileMessages(idMessage) ON DELETE CASCADE ON UPDATE CASCADE, "
				+ "FOREIGN KEY (receivers) REFERENCES Employees(nEmployee) ON DELETE CASCADE ON UPDATE CASCADE);";
		Statement oStatementT;
		try {
			oStatementT = oConnection.createStatement();
			oStatementT.execute(createTableT);
		} catch (SQLException e) {
			throw new DataBaseException();
		}
	}

	private void createTableConferenceM(Connection oConnection)
			throws DataBaseException {
		String createTableC = "CREATE TABLE ConferenceMessages (date TIMESTAMP, "
				+ "text VARCHAR(1000), "
				+ "sender INT, "
				+ "idMessage INT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
				+ "idConference INT NOT NULL, FOREIGN KEY (idConference) REFERENCES Conference(idConference) "
				+ "ON DELETE CASCADE ON UPDATE CASCADE);";
		Statement oStatementC;
		try {
			oStatementC = oConnection.createStatement();
			oStatementC.execute(createTableC);
		} catch (SQLException e) {
			throw new DataBaseException();
		}
	}

	/*
	 * private void createTableConference(Connection oConnection) { String
	 * createTableC = "CREATE TABLE Conferences (nConference INT NOT NULL, " +
	 * "participant INT REFERENCES Employees(nEmployee), PRIMARY KEY (nConference, participant));"
	 * ; Statement oStatementC; try { oStatementC =
	 * oConnection.createStatement(); oStatementC.execute(createTableC); } catch
	 * (SQLException e) { e.printStackTrace(); } }
	 */

	@Override
	public Connection openConnection() throws DataBaseException {
		Connection oConnection = null;
		try {
			oConnection = DataBaseConnectionMgr.getInstance().getConnection();
		} catch (SQLException e2) {
			throw new DataBaseException();
		}
		return oConnection;
	}

	private void addTextMessage(Message oMessage) throws DataBaseException {
		Connection oConnection = this.openConnection();
		String insert = "INSERT INTO TextMessages (sender, date, text) VALUES (?, ?, ?)";
		PreparedStatement oPreparedStatement;
		Statement oStatement;

		try {
			oStatement = oConnection.createStatement();
			oPreparedStatement = oConnection.prepareStatement(insert);

			oPreparedStatement.setInt(1, oMessage.getSender().getnEmployee());
			oPreparedStatement.setTimestamp(2, oMessage.getDate());
			oPreparedStatement.setString(3, ((TextMessage) oMessage).getText());

			oPreparedStatement.execute();
			int idMessage = getIdMessage(oConnection);
			for (int i = 0; i < oMessage.getReceivers().size(); i++) {
				String insert2 = "INSERT INTO TextEmployees (idMessage, receivers) VALUES ('"
						+ idMessage
						+ "', '"
						+ oMessage.getReceivers().get(i).getnEmployee() + "');";
				oStatement.execute(insert2);
			}

		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void addFileMessage(Message oMessage) throws DataBaseException {
		Connection oConnection = this.openConnection();
		String insert = "INSERT INTO FileMessages (date, sender, file, name) VALUES (?, ?, ?, ?)";
		PreparedStatement oPreparedStatement;
		Statement oStatement;

		try {
			oStatement = oConnection.createStatement();
			oPreparedStatement = oConnection.prepareStatement(insert);
			/*
			 * Blob oBlob = oConnection.createBlob(); if (((FileMessage)
			 * oMessage).getData() != null) { oBlob.setBytes(1, ((FileMessage)
			 * oMessage).getData()); } else { oBlob = null; // PODRIA FALLAR!! }
			 */

			oPreparedStatement.setTimestamp(1, oMessage.getDate());
			oPreparedStatement.setInt(2, oMessage.getSender().getnEmployee());
			oPreparedStatement.setBytes(3, ((FileMessage) oMessage).getData());
			oPreparedStatement.setString(4, ((FileMessage) oMessage).getName());

			oPreparedStatement.execute();
			int idFileMessage = getIdFileMessage(oConnection);
			for (int i = 0; i < oMessage.getReceivers().size(); i++) {
				String insert2 = "INSERT INTO FileEmployees (idMessage, receivers) VALUES ("
						+ idFileMessage
						+ ", "
						+ oMessage.getReceivers().get(i).getnEmployee() + ");";
				oStatement.execute(insert2);
			}
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void addConferenceMessage(Message oMessage)
			throws DataBaseException {
		Connection oConnection = this.openConnection();
		String execute = "INSERT INTO ConferenceMessages (date, text, sender, idConference) VALUES (?, ?, ?, ?)";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(execute);
			oPreparedStatement.setTimestamp(1, oMessage.getDate());
			oPreparedStatement.setString(2,
					((ConferenceMessage) oMessage).getText());
			oPreparedStatement.setInt(3, oMessage.getSender().getnEmployee());
			oPreparedStatement.setInt(4,
					((ConferenceMessage) oMessage).getIdConference());
			oPreparedStatement.execute();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private int getIdMessage(Connection oConnection) throws DataBaseException {
		String getCounter = "SELECT COUNT(*) FROM TextMessages;";
		int result = 1;
		Statement oStatement;
		try {
			oStatement = oConnection.createStatement();
			ResultSet oResult = oStatement.executeQuery(getCounter);
			while (oResult.next()) {
				result = oResult.getInt(1);
			}
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		}
		return result;
	}

	private int getIdFileMessage(Connection oConnection)
			throws DataBaseException {
		String getCounter = "SELECT COUNT(*) FROM FileMessages;";
		int result = 1;
		Statement oStatement;
		try {
			oStatement = oConnection.createStatement();
			ResultSet oResult = oStatement.executeQuery(getCounter);
			while (oResult.next()) {
				result = oResult.getInt(1);
			}
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		}
		return result;
	}

	@Override
	public ArrayList<ConferenceMessage> getConferenceMessages(int idConference)
			throws DataBaseException {
		ArrayList<ConferenceMessage> result = new ArrayList<ConferenceMessage>();
		Connection oConnection = this.openConnection();
		String executeThis = "SELECT date, sender, text FROM ConferenceMessages "
				+ "WHERE idConference = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(executeThis);
			oPreparedStatement.setInt(1, idConference);
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				int nEmployee = oResult.getInt(2);
				String execute2 = "SELECT e.name, e.surname, e.document, "
						+ "e.nEmployee, e.positionInCompany, e.currentCountry, e.branch, "
						+ "e.department, e.username, e.password, e.documentType, e.image, "
						+ "e.availability, e.email, e.administrator, e.salt FROM Employees e WHERE nEmployee = ?";
				PreparedStatement oPreparedStatement2 = oConnection
						.prepareStatement(execute2);
				oPreparedStatement2.setInt(1, nEmployee);
				ResultSet oResult2 = oPreparedStatement2.executeQuery();
				Employee oEmployee2 = null;
				while (oResult2.next()) {
					if (oResult2.getBlob(12) == null) {
						oEmployee2 = new Employee(oResult2.getString(1),
								oResult2.getString(2), oResult2.getInt(3),
								oResult2.getInt(4), oResult2.getString(5),
								oResult2.getString(6), oResult2.getString(7),
								oResult2.getString(8), oResult2.getString(9),
								oResult2.getString(10), oResult2.getString(11),
								null, oResult2.getString(13),
								oResult2.getString(14),
								oResult2.getBoolean(15), oResult2.getString(16));
					} else {
						oEmployee2 = new Employee(oResult2.getString(1),
								oResult2.getString(2), oResult2.getInt(3),
								oResult2.getInt(4), oResult2.getString(5),
								oResult2.getString(6), oResult2.getString(7),
								oResult2.getString(8), oResult2.getString(9),
								oResult2.getString(10), oResult2.getString(11),
								oResult2.getBlob(12).getBytes(1,
										(int) oResult2.getBlob(12).length()),
								oResult2.getString(13), oResult2.getString(14),
								oResult2.getBoolean(15), oResult2.getString(16));
					}

				}
				oResult2.close();
				ConferenceMessage oConference = new ConferenceMessage(
						oResult.getTimestamp(1), oEmployee2,
						oResult.getString(3), idConference);
				result.add(oConference);
			}
			oResult.close();

		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}

	@Override
	public ArrayList<Employee> getLastEmployees(int nEmployee)
			throws DataBaseException {
		ArrayList<Employee> oList = new ArrayList<Employee>();
		String secondPart = "SELECT f.date, f.sender, e.receivers FROM FileMessages f, FileEmployees e "
				+ "WHERE f.idMessage = e.idMessage AND (f.sender = ? OR e.receivers = ?) ORDER BY date DESC;";
		String executeThis = "SELECT t.date, t.sender, e.receivers FROM TextMessages t, "
				+ "TextEmployees e WHERE t.idMessage = e.idMessage AND (t.sender = ? OR e.receivers = ?) UNION  "
				+ secondPart;
		Connection oConnection = this.openConnection();
		ArrayList<Integer> oListInteger = new ArrayList<Integer>();
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(executeThis);
			oPreparedStatement.setInt(1, nEmployee);
			oPreparedStatement.setInt(2, nEmployee);
			oPreparedStatement.setInt(3, nEmployee);
			oPreparedStatement.setInt(4, nEmployee);
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				if (oResult.getInt(2) == nEmployee) {
					if (!oListInteger.contains(oResult.getInt(3))) {
						oListInteger.add(oResult.getInt(3));
					}
				} else {
					if (!oListInteger.contains(oResult.getInt(2))) {
						oListInteger.add(oResult.getInt(2));
					}
				}
			}
			for (int j = 0; j < oListInteger.size(); j++) {
				String getEmployee = "SELECT e.name, e.surname, e.document, "
						+ "e.nEmployee, e.positionInCompany, e.currentCountry, e.branch, "
						+ "e.department, e.username, e.password, e.documentType, e.image, "
						+ "e.availability, e.email, e.administrator, e.salt FROM Employees e WHERE nEmployee = "
						+ oListInteger.get(j);
				Statement oStatement;
				oStatement = oConnection.createStatement();
				ResultSet oResult2 = oStatement.executeQuery(getEmployee);
				Employee oEmployee2 = null;
				while (oResult2.next()) {
					if (oResult2.getBlob(12) == null) {
						oEmployee2 = new Employee(oResult2.getString(1),
								oResult2.getString(2), oResult2.getInt(3),
								oResult2.getInt(4), oResult2.getString(5),
								oResult2.getString(6), oResult2.getString(7),
								oResult2.getString(8), oResult2.getString(9),
								oResult2.getString(10), oResult2.getString(11),
								null, oResult2.getString(13),
								oResult2.getString(14),
								oResult2.getBoolean(15), oResult2.getString(16));
					} else {
						oEmployee2 = new Employee(oResult2.getString(1),
								oResult2.getString(2), oResult2.getInt(3),
								oResult2.getInt(4), oResult2.getString(5),
								oResult2.getString(6), oResult2.getString(7),
								oResult2.getString(8), oResult2.getString(9),
								oResult2.getString(10), oResult2.getString(11),
								oResult2.getBlob(12).getBytes(1,
										(int) oResult2.getBlob(12).length()),
								oResult2.getString(13), oResult2.getString(14),
								oResult2.getBoolean(15), oResult2.getString(16));
					}
				}
				oResult2.close();
				oList.add(oEmployee2);
			}
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
		return oList;
	}

	public ArrayList<TextMessage> getMessagesWithTheWord(String word)
			throws DataBaseException {
		ArrayList<TextMessage> oList = new ArrayList<TextMessage>();
		String execute = "SELECT t.date, t.text, t.sender, e.receivers FROM TextMessages t, "
				+ "TextEmployees e WHERE t.idMessage = e.idMessage AND t.text LIKE ? ORDER BY t.date DESC;";
		PreparedStatement oPreparedStatement;
		Connection oConnection = this.openConnection();
		try {
			oPreparedStatement = oConnection.prepareStatement(execute);
			oPreparedStatement.setString(1, "%" + word + "%");
			ResultSet oResultSet = oPreparedStatement.executeQuery();
			while (oResultSet.next()) {
				Employee oSender = getEmployee(oConnection,
						oResultSet.getInt(3));
				Employee oReceiver = getEmployee(oConnection,
						oResultSet.getInt(4));
				ArrayList<Employee> receivers = new ArrayList<Employee>();
				receivers.add(oReceiver);
				TextMessage oText = new TextMessage(oResultSet.getTimestamp(1),
						receivers, oSender, oResultSet.getString(2));
				oList.add(oText);
			}
			oResultSet.close();
			oPreparedStatement.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
		return oList;
	}

	private Employee getEmployee(Connection oConnection, int nEmployee)
			throws DataBaseException {
		String getEmployee = "SELECT e.name, e.surname, e.document, "
				+ "e.nEmployee, e.positionInCompany, e.currentCountry, e.branch, "
				+ "e.department, e.username, e.password, e.documentType, e.image, "
				+ "e.availability, e.email, e.administrator, e.salt FROM Employees e WHERE nEmployee = "
				+ nEmployee;
		Statement oStatement;
		Employee oEmployee2 = null;
		try {
			oStatement = oConnection.createStatement();
			ResultSet oResult2 = oStatement.executeQuery(getEmployee);
			while (oResult2.next()) {
				if (oResult2.getBlob(12) == null) {
					oEmployee2 = new Employee(oResult2.getString(1),
							oResult2.getString(2), oResult2.getInt(3),
							oResult2.getInt(4), oResult2.getString(5),
							oResult2.getString(6), oResult2.getString(7),
							oResult2.getString(8), oResult2.getString(9),
							oResult2.getString(10), oResult2.getString(11),
							null, oResult2.getString(13),
							oResult2.getString(14), oResult2.getBoolean(15),
							oResult2.getString(16));
				} else {
					oEmployee2 = new Employee(oResult2.getString(1),
							oResult2.getString(2), oResult2.getInt(3),
							oResult2.getInt(4), oResult2.getString(5),
							oResult2.getString(6), oResult2.getString(7),
							oResult2.getString(8), oResult2.getString(9),
							oResult2.getString(10), oResult2.getString(11),
							oResult2.getBlob(12).getBytes(1,
									(int) oResult2.getBlob(12).length()),
							oResult2.getString(13), oResult2.getString(14),
							oResult2.getBoolean(15), oResult2.getString(16));
				}
			}
			oResult2.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		}
		return oEmployee2;
	}

	public int getAmountOfWordsWritten(int nEmployee, Timestamp date1,
			Timestamp date2) throws DataBaseException {
		
		Connection oConnection = this.openConnection();
		int result = 0;
		try {
			
			ArrayList<String> listOfStrings = getMessagesWrittenBy(oConnection,
					nEmployee, date1, date2);
			this.createFunctionForSpaces(oConnection);

			for (int i = 0; i < listOfStrings.size(); i++) {
				String text = this.getTextWithOneSpace(oConnection,
						listOfStrings.get(i));
				String executeThis = "SELECT SUM((length('" + text
						+ "')-length(replace('" + text + "',' ',''))+1))";

				Statement oStatement = oConnection.createStatement();
				ResultSet oResult = oStatement.executeQuery(executeThis);
				while (oResult.next()) {
					result = result + oResult.getInt(1);
				}
				oStatement.close();
				oResult.close();
			}
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return result;
	}

	private ArrayList<String> getMessagesWrittenBy(Connection oConnection,
			int nEmployee, Timestamp date1, Timestamp date2)
			throws DataBaseException {
		ArrayList<String> oList = new ArrayList<String>();
		String execute = "SELECT text FROM TextMessages WHERE sender = ? AND (date BETWEEN ? AND ?)";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(execute);
			oPreparedStatement.setInt(1, nEmployee);
			oPreparedStatement.setTimestamp(2, date1);
			oPreparedStatement.setTimestamp(3, date2);
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				String sMessage = oResult.getString(1);
				oList.add(sMessage);
			}
			oPreparedStatement.close();
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		}
		return oList;
	}

	private void createFunctionForSpaces(Connection oConnection)
			throws DataBaseException {
		Statement oStatement;
		try {
			oStatement = oConnection.createStatement();
			String execute = "DROP FUNCTION IF EXISTS DELETE_DOUBLE_SPACES;";
			oStatement.execute(execute);
			oStatement.close();
			String executeThisToo = "CREATE FUNCTION DELETE_DOUBLE_SPACES ( title VARCHAR(1000) ) RETURNS VARCHAR(1000) DETERMINISTIC BEGIN DECLARE result VARCHAR(1000); SET result = REPLACE( title, '  ', ' ' ); WHILE (result <> title) DO SET title = result; SET result = REPLACE( title, '  ', ' ' ); END WHILE; RETURN result; END;";
			Statement oStatement2 = oConnection.createStatement();
			oStatement2.execute(executeThisToo);
			oStatement2.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		}
	}

	private String getTextWithOneSpace(Connection oConnection, String text)
			throws DataBaseException {
		String result = "";
		try {
			String executeThisToo = "SELECT DELETE_DOUBLE_SPACES(?);";
			PreparedStatement oPreparedStatement = oConnection
					.prepareStatement(executeThisToo);
			oPreparedStatement.setString(1, text);
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				result = oResult.getString(1);
			}
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		}
		return result;
	}

	@Override
	public ArrayList<Integer> getFilesByOneEmployee(int employee1)
			throws DataBaseException {
		Connection oConnection = this.openConnection();
		ArrayList<Integer> oList = new ArrayList<Integer>();
		String execute = "SELECT idMessage FROM FileMessages f, FileEmployees e WHERE e.idMessage = f.idMessage "
				+ "AND receivers = ? ORDER BY date ";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(execute);
			oPreparedStatement.setInt(1, employee1);
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				Integer oInt = oResult.getInt(1);
				oList.add(oInt);
			}
			oPreparedStatement.close();
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
		return oList;
	}

	@Override
	public ArrayList<Integer> getFilesFromOneEmployeeToAnother(int sender,
			int receiver) throws DataBaseException {
		Connection oConnection = this.openConnection();
		ArrayList<Integer> oList = new ArrayList<Integer>();
		String execute = "SELECT f.idMessage FROM FileMessages f, FileEmployees e WHERE e.idMessage = f.idMessage "
				+ "AND receivers = ? AND sender = ? ORDER BY date ";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(execute);
			oPreparedStatement.setInt(1, receiver);
			oPreparedStatement.setInt(2, sender);
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				Integer oInt = oResult.getInt(1);
				oList.add(oInt);
			}
			oPreparedStatement.close();
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
		return oList;
	}

	public String getNameOfFile(int id) throws DataBaseException {
		String execute = "SELECT e.name FROM FileMessages e WHERE e.idMessage = ?";
		PreparedStatement oPreparedStatement;
		String name = null;
		Connection oConnection = this.openConnection();
		try {
			oPreparedStatement = oConnection.prepareStatement(execute);
			oPreparedStatement.setInt(1, id);
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				name = oResult.getString(1);
			}
			oPreparedStatement.close();
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
		return name;
	}

	public byte[] getFile(int id) throws DataBaseException {
		String execute = "SELECT e.file FROM FileMessages e WHERE e.idMessage = ?";
		PreparedStatement oPreparedStatement;
		byte[] file = null;
		Connection oConnection = this.openConnection();
		try {
			oPreparedStatement = oConnection.prepareStatement(execute);
			oPreparedStatement.setInt(1, id);
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				file = oResult.getBlob(1).getBytes(1,
						(int) oResult.getBlob(1).length());
			}
			oPreparedStatement.close();
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
		return file;
	}

}